--sp_AI_BPBalance '3/1/2013'
ALTER proc [dbo].[sp_AI_BPBalance]
@Date date
as
select T0.CardCode,T0.CardName, T1.BalanceDue,  T4.OverDueDaysAFL  MaxOverDue ,
case when T3.CardCode is Not null then -- 'License Fee'
		CASE when T4.OverDueDaysAFL>=90 then 'ALF Final Reminder'
		when T4.OverDueDaysAFL> 60 and T4.OverDueDaysAFL<90 then 'ALF Second Reminder'
		when T4.OverDueDaysAFL > 30 and T4.OverDueDaysAFL <=60 then 'ALF Reminder' end
else
		CASE when T4.OverDueDaysAFL>=90 then 'Final Reminder'
		when T4.OverDueDaysAFL> 60 and T4.OverDueDaysAFL<90 then 'Second Reminder'
		when T4.OverDueDaysAFL > 30 and T4.OverDueDaysAFL <=60 then 'Reminder' end
end  LayOut, T0.U_AI_InvContactCode
from OCRD T0 with(nolock)
join
(
	select T2.CardCode,
	sum(Debit-(case when isCredit='D' then isnull(ReconSum,0) else 0 end)-(Credit-(case when isCredit='C' then isnull(ReconSum,0) else 0 end))) BalanceDue  
	from JDT1 T1 with (nolock) 
	join OCRD T2 with (nolock) on T1.ShortName=T2.CardCode  
	join OJDT T3 with (nolock) on T3.TransID=T1.TransID  
	left join
	(  
		select t1.TransID, t1.TransRowID, sum(isnull(t1.ReconSum,0)) ReconSum, t1.isCredit  
		from ITR1 t1 with (nolock) join OITR t2 with (nolock) on T1.ReconNum=T2.ReconNum  
		where t2.ReconDate<=@Date   
		group by t1.TransID, T1.TransRowID, T1.isCredit   
	) T4 on T1.TransID=T4.TransID and T1.Line_ID=T4.TransRowID 
	where T3.RefDate<=@Date
	group by CardCode
) T1 on T0.CardCode=T1.CardCode
--join
--(
--	select T2.CardCode,
--	max(DATEDIFF(dd,isnull(T4.ReconDate,T1.DueDate),@date)) OverDueDays
--	from JDT1 T1 with (nolock) 
--	join OCRD T2 with (nolock) on T1.ShortName=T2.CardCode  
--	join OJDT T3 with (nolock) on T3.TransID=T1.TransID  
--	left join
--	(  
--		select t1.TransID, t1.TransRowID, t2.ReconDate, t1.isCredit  
--		from ITR1 t1 with (nolock) 
--		join OITR t2 with (nolock) on T1.ReconNum=T2.ReconNum  
--		where t2.ReconDate<=@Date   
--	) T4 on T1.TransID=T4.TransID and T1.Line_ID=T4.TransRowID 
--	where T3.RefDate<=@Date and T4.TransId is null
--	group by CardCode 
--) T2 on T0.CardCode=T2.CardCode
left join
(
	select distinct T0.CardCode from OINV T0
	join INV1 T1 on T0.DocEntry=T1.DocEntry
	join OITM T2 on T1.ItemCode=T2.ItemCode
	where T0.DocDate<=@Date
		and T2.U_AI_ALF='Y'
) T3 on T3.CardCode=T0.CardCode

join
(
	select T2.CardCode,
	max(DATEDIFF(dd,isnull(T4.ReconDate,T1.TaxDate),@date)) OverDueDaysAFL
	from JDT1 T1 with (nolock) 
	join OCRD T2 with (nolock) on T1.ShortName=T2.CardCode  
	join OJDT T3 with (nolock) on T3.TransID=T1.TransID  
	left join
	(  
		select t1.TransID, t1.TransRowID, t2.ReconDate, t1.isCredit  
		from ITR1 t1 with (nolock) 
		join OITR t2 with (nolock) on T1.ReconNum=T2.ReconNum  
		where t2.ReconDate<=@Date   
	) T4 on T1.TransID=T4.TransID and T1.Line_ID=T4.TransRowID 
	where T1.TaxDate<=@Date and T4.TransId is null
	group by CardCode 
) T4 on T0.CardCode=T4.CardCode


where CardType='C' and T1.BalanceDue<>0
and (T4.OverDueDaysAFL>30 or T3.CardCode is Not null)



GO


